<?php
/*
* Model for all the payment recrods
*
*/

class M_payment_report Extends MY_Model
{

	private $_t = 'payment_reports';
	
	/* insert New Set Payment Reports*/
	public function insert_payment_report($data)
	{
		$data['pr_date_created'] = NOW;
		$this->db->insert($this->_t,$data);
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_payment_report($id = FALSE)
	{
		if($id == FALSE)
		{
			$sql = "select pr_id,pr.pr_date_start,pr.pr_date_end,l.level_desc,pr.pr_grade_levels
					FROM payment_reports pr
					LEFT JOIN levels l ON l.level_id = pr.pr_grade_levels";
			$query = $this->db->query($sql);
			return $query->num_rows() >=1 ? $query->result() : FALSE;
		}else{
			if(is_numeric($id))
			{
				$sql = "select pr_id,pr.pr_date_start,pr.pr_date_end,l.level_desc,pr.pr_grade_levels
					FROM payment_reports pr
					LEFT JOIN levels l ON l.level_id = pr.pr_grade_levels
					WHERE pr_id = ?";
				$query = $this->db->query($sql,array($id));
				return $query->num_rows() >=1 ? $query->row() : FALSE;
			}else{
				return FALSE;
			}
		}
	}
	
	
	public function get_report_result($x)
	{
		/* get Enrollment Id of payments from date start to date end */
		$get_payment = "SELECT spr.spr_enrollment_id as e_id,
								concat(p.firstname,' ',p.lastname) as fullname,
								e.e_student_id as idno,
								sum(spr_ammt_paid) as total_payments
						FROM student_payment_records spr
						LEFT JOIN enrollments e ON e.e_id = spr.spr_enrollment_id
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						WHERE spr.spr_payment_date >= ? AND spr.spr_payment_date <= ?
						";
		if($x->pr_grade_levels == 0)
		{
			$get_payment .= ' GROUP BY spr.spr_enrollment_id';
			$query_get_payment = $this->db->query($get_payment,array($x->pr_date_start,$x->pr_date_end));
		}else{
			$get_payment .= ' AND e.e_grade_level = ? GROUP BY spr.spr_enrollment_id';
			$query_get_payment = $this->db->query($get_payment,array($x->pr_date_start,$x->pr_date_end,$x->pr_grade_levels));
		}		
		$result = $query_get_payment->num_rows() >=1 ? $query_get_payment->result() : FALSE;
		
		if(!empty($result))
		{
			foreach($result as $k =>  $v)
			{
				$record_set[$k]['fullname'] = $v->fullname;
				$record_set[$k]['idno'] = $v->idno;
				$record_set[$k]['total_payments'] = $v->total_payments;
				
				$fee_breakdown = $this->get_breakdown_of_fees_by_enrollment_id($v->e_id);
				
				foreach($fee_breakdown as $key => $val)
				{
					$record_set[$k]['fees'][$key]['spb_is_tuition_fee'] = $val->spb_is_tuition_fee;
					$record_set[$k]['fees'][$key]['spb_fee_name'] = $val->spb_fee_name;
					$record_set[$k]['fees'][$key]['paid'] = $val->paid;
					$record_set[$k]['fees'][$key]['spb_created'] = $val->spb_created;
				}
			}
			return (object)$record_set;
		}else{
			return FALSE;
		}
	}
	
	private function get_breakdown_of_fees_by_enrollment_id($id)
	{
		$get_breakdown = "select spb_is_tuition_fee, spb_fee_name,sum(spb_amount_paid) as paid,spb_created
				FROM student_payment_breakdown
				WHERE spb_stud_enrollment_id = ?
				GROUP BY spb_fee_name
				ORDER BY spb_is_tuition_fee DESC";
		$query_get_breakdown = $this->db->query($get_breakdown,array($id));
		return $query_get_breakdown->num_rows() >=1 ? $query_get_breakdown->result() : FALSE;
	}

}